﻿using Sc.Domain;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Sc.Persistence
{
   public class CarInfoDAO
    {
        /// <summary>
        /// 增加一条数据
        /// </summary>
       public int Add(CarInfo model)
       {
           StringBuilder strSql = new StringBuilder();
           strSql.Append("insert into CarInfo(");
           strSql.Append("CarNo,Driver,GPSNo,CarState,TakeCount,UseRange,CarType,CarNature,State,HasAttachments,ChassisNumber,CarColor,CarDisplacement,StarMileage,Maintenance,ImageUrl,OrgzNo,ZcTonnage,BuyTime,ServiceLife)");
           strSql.Append(" values (");
           strSql.Append("@CarNo,@Driver,@GPSNo,@CarState,@TakeCount,@UseRange,@CarType,@CarNature,@State,@HasAttachments,@ChassisNumber,@CarColor,@CarDisplacement,@StarMileage,@Maintenance,@ImageUrl,@OrgzNo,@ZcTonnage,@BuyTime,@ServiceLife)");
           strSql.Append(";select @@IDENTITY");
           SqlParameter[] parameters = {
					new SqlParameter("@CarNo", SqlDbType.NVarChar,50),
					new SqlParameter("@Driver", SqlDbType.NVarChar,50),
					new SqlParameter("@GPSNo", SqlDbType.NVarChar,50),
					new SqlParameter("@CarState", SqlDbType.Int,4),
					new SqlParameter("@TakeCount", SqlDbType.Int,4),
					new SqlParameter("@UseRange", SqlDbType.NVarChar,50),
					new SqlParameter("@CarType", SqlDbType.Int,4),
					new SqlParameter("@CarNature", SqlDbType.Int,4),
					new SqlParameter("@State", SqlDbType.Int,4),
					new SqlParameter("@HasAttachments", SqlDbType.Bit,1),
					new SqlParameter("@ChassisNumber", SqlDbType.NVarChar,100),
					new SqlParameter("@CarColor", SqlDbType.NVarChar,50),
					new SqlParameter("@CarDisplacement", SqlDbType.NVarChar,50),
					new SqlParameter("@StarMileage", SqlDbType.Decimal,9),
					new SqlParameter("@Maintenance", SqlDbType.Int,4),
					new SqlParameter("@ImageUrl", SqlDbType.NVarChar,150),
                    new SqlParameter("@OrgzNo", SqlDbType.Int),
                    new SqlParameter("@ZcTonnage", SqlDbType.Decimal),
                    new SqlParameter("@BuyTime", SqlDbType.Date),
                    new SqlParameter("@ServiceLife", SqlDbType.Int)};
           parameters[0].Value = model.CarNo;
           parameters[1].Value = model.Driver;
           parameters[2].Value = model.GPSNo;
           parameters[3].Value = model.CarState;
           parameters[4].Value = model.TakeCount;
           parameters[5].Value = model.UseRange;
           parameters[6].Value = model.CarType;
           parameters[7].Value = model.CarNature;
           parameters[8].Value = model.State;
           parameters[9].Value = model.HasAttachments;
           parameters[10].Value = model.ChassisNumber;
           parameters[11].Value = model.CarColor;
           parameters[12].Value = model.CarDisplacement;
           parameters[13].Value = model.StarMileage;
           parameters[14].Value = model.Maintenance;
           parameters[15].Value = model.ImageUrl;
           parameters[16].Value = model.OrgzNo;
           parameters[17].Value = model.ZcTonnage;
           parameters[18].Value = model.BuyTime;
           parameters[19].Value = model.ServiceLife;
           object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
           if (obj == null)
           {
               return 0;
           }
           else
           {
               return Convert.ToInt32(obj);
           }
       }
       /// <summary>
       /// 更新一条数据
       /// </summary>
       public bool Update(CarInfo model)
       {
           StringBuilder strSql = new StringBuilder();
           strSql.Append("update CarInfo set ");
           strSql.Append("CarNo=@CarNo,");
           strSql.Append("Driver=@Driver,");
           strSql.Append("GPSNo=@GPSNo,");
           strSql.Append("CarState=@CarState,");
           strSql.Append("TakeCount=@TakeCount,");
           strSql.Append("UseRange=@UseRange,");
           strSql.Append("CarType=@CarType,");
           strSql.Append("CarNature=@CarNature,");
           strSql.Append("State=@State,");
           strSql.Append("HasAttachments=@HasAttachments,");
           strSql.Append("ChassisNumber=@ChassisNumber,");
           strSql.Append("CarColor=@CarColor,");
           strSql.Append("CarDisplacement=@CarDisplacement,");
           strSql.Append("StarMileage=@StarMileage,");
           strSql.Append("Maintenance=@Maintenance,");
           strSql.Append("ZcTonnage=@ZcTonnage,");
           strSql.Append("BuyTime=@BuyTime,");
           strSql.Append("ServiceLife=@ServiceLife");
           strSql.Append(" where Id=@Id");
           SqlParameter[] parameters = {
					new SqlParameter("@CarNo", SqlDbType.NVarChar,50),
					new SqlParameter("@Driver", SqlDbType.NVarChar,50),
					new SqlParameter("@GPSNo", SqlDbType.NVarChar,50),
					new SqlParameter("@CarState", SqlDbType.Int,4),
					new SqlParameter("@TakeCount", SqlDbType.Int,4),
					new SqlParameter("@UseRange", SqlDbType.NVarChar,50),
					new SqlParameter("@CarType", SqlDbType.Int,4),
					new SqlParameter("@CarNature", SqlDbType.Int,4),
					new SqlParameter("@State", SqlDbType.Int,4),
					new SqlParameter("@HasAttachments", SqlDbType.Bit,1),
					new SqlParameter("@ChassisNumber", SqlDbType.NVarChar,100),
					new SqlParameter("@CarColor", SqlDbType.NVarChar,50),
					new SqlParameter("@CarDisplacement", SqlDbType.NVarChar,50),
					new SqlParameter("@StarMileage", SqlDbType.Decimal,9),
					new SqlParameter("@Maintenance", SqlDbType.Int,4),
					new SqlParameter("@Id", SqlDbType.Int,4),
                    new SqlParameter("@ZcTonnage", SqlDbType.Decimal),
                    new SqlParameter("@BuyTime", SqlDbType.Date),
                    new SqlParameter("@ServiceLife", SqlDbType.Int)};
           parameters[0].Value = model.CarNo;
           parameters[1].Value = model.Driver;
           parameters[2].Value = model.GPSNo;
           parameters[3].Value = model.CarState;
           parameters[4].Value = model.TakeCount;
           parameters[5].Value = model.UseRange;
           parameters[6].Value = model.CarType;
           parameters[7].Value = model.CarNature;
           parameters[8].Value = model.State;
           parameters[9].Value = model.HasAttachments;
           parameters[10].Value = model.ChassisNumber;
           parameters[11].Value = model.CarColor;
           parameters[12].Value = model.CarDisplacement;
           parameters[13].Value = model.StarMileage;
           parameters[14].Value = model.Maintenance;
           parameters[15].Value = model.Id;
           parameters[16].Value = model.ZcTonnage;
           parameters[17].Value = model.BuyTime;
           parameters[18].Value = model.ServiceLife;
           int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
           if (rows > 0)
           {
               return true;
           }
           else
           {
               return false;
           }
       }
       /// <summary>
       /// 删除一条数据
       /// </summary>
       public bool Delete(int Id)
       {

           StringBuilder strSql = new StringBuilder();
           strSql.Append("delete from CarInfo ");
           strSql.Append(" where Id=@Id");
           SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4)
			};
           parameters[0].Value = Id;

           int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
           if (rows > 0)
           {
               return true;
           }
           else
           {
               return false;
           }
       }
       /// <summary>
       /// 批量删除数据
       /// </summary>
       public bool DeleteList(string Idlist)
       {
           StringBuilder strSql = new StringBuilder();
           strSql.Append("delete from CarInfo ");
           strSql.Append(" where Id in (" + Idlist + ")  ");
           int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
           if (rows > 0)
           {
               return true;
           }
           else
           {
               return false;
           }
       }
       /// <summary>
       /// 获得数据列表
       /// <param name="where">查询条件</param>
       /// </summary>
       public IList<CarInfo> GetList(string where)
       {
           IList<CarInfo> result = new List<CarInfo>();
           string sql = "SELECT * FROM CarInfo WHERE 1=1 " + where;
           using (SqlDataReader dr = DbHelperSQL.ExecuteReader(sql))
           {
               while (dr.Read())
               {
                   result.Add(BuilderModel(dr));
               }
               dr.Close();
           }
           return result;
       }
       /// <summary>
       /// 获取单个模型
       /// </summary>
       /// <param name="id">业务ID</param>
       /// <returns></returns>
       public CarInfo GetCarInfoById(int id)
       {
           string sql = "SELECT * FROM CarInfo WHERE Id=@Id";
           SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4)
			};
           parameters[0].Value = id;
           CarInfo ent = null;
           using (SqlDataReader dr = DbHelperSQL.ExecuteReader(sql, parameters))
           {
               if (dr.Read())
               {
                   ent = BuilderModel(dr);
               }
           }
           return ent;
       }
       /// <summary>
       /// 更新车辆状态
       /// </summary>
       /// <param name="carNo">车辆ID</param>
       /// <param name="state">状态</param>
       /// <returns></returns>
       public int UpdateCarState(int carNo,int state)
       {
           var sql = "UPDATE CarInfo SET CarState=@CarState WHERE Id=@Id";
           SqlParameter[] param = new SqlParameter[]{
               new SqlParameter("@CarState",SqlDbType.Int),
               new SqlParameter("@Id",SqlDbType.Int)
           };
           param[0].Value = state;
           param[1].Value = carNo;
           return DbHelperSQL.ExecuteSql(sql, param);
       }
       /// <summary>
       /// 组装模型
       /// </summary>
       /// <param name="dr"></param>
       /// <returns></returns>
       private CarInfo BuilderModel(IDataReader dr)
       {
           try
           {
               CarInfo obj = new CarInfo();
               obj.Id = (dr["Id"] == System.DBNull.Value ? 0 : dr.GetInt32(dr.GetOrdinal("Id")));
               obj.CarNo = (dr["CarNo"] == System.DBNull.Value ? String.Empty : dr.GetString(dr.GetOrdinal("CarNo")));
               obj.Driver = (dr["Driver"] == System.DBNull.Value ? 0 : dr.GetInt32(dr.GetOrdinal("Driver")));
               obj.GPSNo = (dr["GPSNo"] == System.DBNull.Value ? String.Empty : dr.GetString(dr.GetOrdinal("GPSNo")));
               obj.CarState = (dr["CarState"] == System.DBNull.Value ? 0 : dr.GetInt32(dr.GetOrdinal("CarState")));
               obj.TakeCount = (dr["TakeCount"] == System.DBNull.Value ? 0 : dr.GetInt32(dr.GetOrdinal("TakeCount")));
               obj.UseRange = (dr["UseRange"] == System.DBNull.Value ? String.Empty : dr.GetString(dr.GetOrdinal("UseRange")));
               obj.CarType = (dr["CarType"] == System.DBNull.Value ? 0 : dr.GetInt32(dr.GetOrdinal("CarType")));
               obj.CarNature = (dr["CarNature"] == System.DBNull.Value ? 0 : dr.GetInt32(dr.GetOrdinal("CarNature")));
               obj.State = (dr["State"] == System.DBNull.Value ? 0 : dr.GetInt32(dr.GetOrdinal("State")));
               obj.HasAttachments = (dr["HasAttachments"] == System.DBNull.Value ? false : dr.GetBoolean(dr.GetOrdinal("HasAttachments")));
               obj.ChassisNumber = (dr["ChassisNumber"] == System.DBNull.Value ? String.Empty : dr.GetString(dr.GetOrdinal("ChassisNumber")));
               obj.CarColor = (dr["CarColor"] == System.DBNull.Value ? String.Empty : dr.GetString(dr.GetOrdinal("CarColor")));
               obj.CarDisplacement = (dr["CarDisplacement"] == System.DBNull.Value ? String.Empty : dr.GetString(dr.GetOrdinal("CarDisplacement")));
               obj.StarMileage = (dr["StarMileage"] == System.DBNull.Value ? 0M : dr.GetDecimal(dr.GetOrdinal("StarMileage")));
               obj.Maintenance = (dr["Maintenance"] == System.DBNull.Value ? 0 : dr.GetInt32(dr.GetOrdinal("Maintenance")));
               obj.ImageUrl = (dr["ImageUrl"] == System.DBNull.Value ? String.Empty : dr.GetString(dr.GetOrdinal("ImageUrl")));
               obj.OrgzNo = (dr["OrgzNo"] == System.DBNull.Value ? 0 : dr.GetInt32(dr.GetOrdinal("OrgzNo")));
               obj.ZcTonnage = (dr["ZcTonnage"] == System.DBNull.Value ? 0M : dr.GetDecimal(dr.GetOrdinal("ZcTonnage")));
               obj.BuyTime = (dr["BuyTime"] == System.DBNull.Value ? DateTime.Now : dr.GetDateTime(dr.GetOrdinal("BuyTime")));
               obj.ServiceLife = (dr["ServiceLife"] == System.DBNull.Value ? 0 : dr.GetInt32(dr.GetOrdinal("ServiceLife")));
               return obj;
           }
           catch (Exception)
           {

           }
           return null;
       }
    }
}
